package com.apexedu.biz.service.impl;


import com.apexedu.biz.Const;
import com.apexedu.framework.util.DBUtil;
import com.apexedu.sys.action.ExcelUserImportAction;
import com.apexedu.sys.service.ExcelImportCustomService;
import com.apexedu.sys.util.CommonQuery;

/**
 * 导入excel猪只 采精信息处理类
 * User: 杨建亮
 */
public class ImportSysUserServiceImpl implements ExcelImportCustomService {

    @Override
    public void customMethodAfterDataIntoTempTable(ExcelUserImportAction action) {
        //点击确定导入按钮前预览数据
        String userloginid = action.getUserloginid();
        String sql = "select count(*) from TEMP_EXCEL e where exists (select 1 from T_SYS_USER a where a.userloginid=e.field2 )";
        // 检查添加的用户是不是已存在，已存在禁止加入。
        int err = DBUtil.count(sql);
        if(err>0){
            DBUtil.executeSQL("update TEMP_EXCEL e set erroinfo='该用户已建立' where exists (select 1 from T_SYS_USER a where a.userloginid=e.field2 )");
        }
        sql = "select count(*) from （select field2 from TEMP_EXCEL e group by e.field2 having count(*)>1 ） ";
        // 检查添加的用户表是不是存在重复添加的数据。
        err = DBUtil.count(sql);
        if(err>0){
            DBUtil.executeSQL(" update TEMP_EXCEL e " +
                              " set erroinfo='该用户重复' " +
                              " where exists (select 1 " +
                                             " from (select field2 " +
                                             " from TEMP_EXCEL e " +
                                             " group by e.field2 having count(*)>1) " +
                                             " a where a.field2=e.field2 )");
        }
    }

    @Override
    public void customMethodBeforeImport(ExcelUserImportAction action) {
        // 先清空正式表(业务上是中间过程表)的数据
       String sql = "delete from T_SYS_USER_EXCEL";
        // 找出在正式表还不存在的个体编号（不存在，说明是新测定记录）。把新测定记录插入正式测定表。猪只测定记录，必须个体表有这个猪只才行。
        DBUtil.executeSQL(sql);
    }

    @Override
    public void customMethodAfterImport(ExcelUserImportAction action) {
        // 从中间过程表，导入正式业务表

        //用户从表
        String ext_sql = " insert into T_SYS_USER_EXT (USERID) " +
                          "  select e.wid " +
                          "  from T_SYS_USER_EXCEL e " +
                          "  where not exists (select 1 from T_SYS_USER a where a.userloginid=e.userloginid )";
        //只导入没有的用户
        DBUtil.executeSQL(ext_sql);

        //权限
        String default_roleid= CommonQuery.getSysProperty(Const.DEFAULT_ROLEID);
        String role_sql = " insert into T_SYS_USER_ROLE (id,userid,roleid) " +
                          "  select SYS_GUID(), e.wid,'" + default_roleid+"'"+
                          "  from T_SYS_USER_EXCEL e " +
                          "  where not exists (select 1 from T_SYS_USER a where a.userloginid=e.userloginid )";
        //只导入没有的用户
        DBUtil.executeSQL(role_sql);
        String default_password= CommonQuery.getSysProperty(Const.DEFAULT_PASSWORD);
        String user_sql = " insert into T_SYS_USER (USERNAME,USERLOGINID,USERPWD,USERTYPE,USERID,USERDEPART,STATE) " +
                " select e.USERNAME,e.USERLOGINID,md5(nvl(e.userpwd,'"+default_password+"')),e.USERTYPE,e.wid,'000','1' " +
                " from T_SYS_USER_EXCEL e " +
                " where not exists (select 1 from T_SYS_USER a where a.userloginid=e.userloginid )";
        //只导入没有的用户
        DBUtil.executeSQL(user_sql);
    }
}
